package com.css.fxfzypg.modules.surveytaskmanage.repository.impl;

import com.css.fxfzypg.modules.surveytaskmanage.entity.FieldSurveyTaskEntity;
import com.css.fxfzypg.modules.surveytaskmanage.repository.FieldSurveyTaskRepository;
import com.css.fxfzypg.modules.surveytaskmanage.vo.FieldSurveyVo;
import com.css.fxfzypg.modules.surveytaskmanage.vo.TaskVo;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.servlet.http.HttpServletRequest;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Repository
public class FieldSurveyTaskRepositoryImpl implements FieldSurveyTaskRepository {
    @Autowired
    JdbcTemplate jdbcTemplate;

    @Autowired
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;


    @Override
    public void saveTask(FieldSurveyTaskEntity fieldSurveyTaskEntity) {
        String sql="insert into ypg_survey_task values(:id, :taskYear, :taskNum, :taskName, :taskArea, :taskRegion, :taskManager, :groupName, :groupLeader, " +
                ":taskStatus, :parentTask, :investigationReport, :delFlag, :createUser, :createTime, :updateUser, :updateTime, :groupId, :parentId, :taskDistrict)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(fieldSurveyTaskEntity);
        namedParameterJdbcTemplate.update(sql, source);
    }
    /*@Override
    public Map<String,Object> getPage(HttpServletRequest request, TaskVo taskVo, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from ypg_survey_task");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0' ");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        int firstIndex=(curPage-1) * pageSize;
        int lastIndex=curPage * pageSize;
        StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        if(!PlatformObjectUtils.isEmpty(taskVo.getTaskNum())){
            whereSql.append(" and task_num = '"+taskVo.getTaskNum()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(taskVo.getTaskName())){
            whereSql.append(" and task_name = '"+taskVo.getTaskName()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(taskVo.getTaskStatus())){
            whereSql.append(" and task_status = '"+taskVo.getTaskStatus()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(taskVo.getProvince()) || !PlatformObjectUtils.isEmpty(taskVo.getCity()) || !PlatformObjectUtils.isEmpty(taskVo.getCounty())){
            whereSql.append(" and (");
            if(!PlatformObjectUtils.isEmpty(taskVo.getProvince())){
                whereSql.append(" task_district like '%"+ taskVo.getProvince() +"%'");
            }
            if(!PlatformObjectUtils.isEmpty(taskVo.getCity())){
                whereSql.append(" or task_district like '%"+ taskVo.getCity() +"%'");
            }
            if(!PlatformObjectUtils.isEmpty(taskVo.getCounty())){
                whereSql.append(" or task_district like '%"+ taskVo.getCounty() +"%'");
            }
            whereSql.append(") ");
        }

        String sqlWhole = sql.append(whereSql).append(orderSql).append(limitSql).toString();
        List<FieldSurveyTaskEntity> GroupEntities = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(FieldSurveyTaskEntity.class));
        Integer total = jdbcTemplate.queryForObject("select count(*) from ypg_survey_task where del_flag = '0' ", Integer.class);
        Map<String,Object> map=new HashMap();
        map.put("total",total);
        map.put("rows",GroupEntities);
        return map;
    }*/
    @Override
    public Map<String,Object> findParentIdByTask(String parentId, TaskVo taskVo,int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from ypg_survey_task");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0' and parent_id = '"+parentId+"'");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        int firstIndex=(curPage-1) * pageSize;
        int lastIndex=curPage * pageSize;
        StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        if(!PlatformObjectUtils.isEmpty(taskVo.getCreateUser())){
            whereSql.append(" and create_user = '"+taskVo.getCreateUser()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(taskVo.getTaskNum())){
            whereSql.append(" and task_num like '%"+taskVo.getTaskNum()+"%'");
        }
        if(!PlatformObjectUtils.isEmpty(taskVo.getTaskName())){
            whereSql.append(" and task_name like '%"+taskVo.getTaskName()+"%'");
        }
        if(!PlatformObjectUtils.isEmpty(taskVo.getTaskStatus())){
            whereSql.append(" and task_status = '"+taskVo.getTaskStatus()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(taskVo.getProvince()) || !PlatformObjectUtils.isEmpty(taskVo.getCity()) || !PlatformObjectUtils.isEmpty(taskVo.getCounty())){
            whereSql.append(" and (");
            if(!PlatformObjectUtils.isEmpty(taskVo.getProvince())){
                whereSql.append(" task_district like '%"+ taskVo.getProvince() +"%'");
            }
            if(!PlatformObjectUtils.isEmpty(taskVo.getCity())){
                whereSql.append(" or task_district like '%"+ taskVo.getCity() +"%'");
            }
            if(!PlatformObjectUtils.isEmpty(taskVo.getCounty())){
                whereSql.append(" or task_district like '%"+ taskVo.getCounty() +"%'");
            }
            whereSql.append(") ");
        }
        String sqlWhole = sql.append(whereSql).append(orderSql).append(limitSql).toString();
        List<FieldSurveyVo> list = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(FieldSurveyVo.class));
        Integer total = jdbcTemplate.queryForObject("select count(*) from ypg_survey_task where del_flag = '0' and parent_id = '"+parentId+"' and create_user = '"+taskVo.getCreateUser()+"' ", Integer.class);
        Map<String,Object> map=new HashMap();
        map.put("total",total);
        map.put("rows",list);
        return map;
    }

    @Override
    public List<FieldSurveyTaskEntity> getSubTask(String parentTaskId) {
        String sql="select * from ypg_survey_task where parent_id = ?";
        List<FieldSurveyTaskEntity> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(FieldSurveyTaskEntity.class),parentTaskId);
        return list;
    }

    @Override
    public FieldSurveyTaskEntity findById(String taskId) {
        String sql="select * from ypg_survey_task where id = ? ";
        FieldSurveyTaskEntity fieldSurveyTaskEntity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(FieldSurveyTaskEntity.class), taskId);
        return fieldSurveyTaskEntity;
    }

    @Override
    public void updateTask(FieldSurveyTaskEntity fieldSurveyTaskEntity) {
        String sql="update ypg_survey_task set task_name = :taskName, update_user = :updateUser, update_time = :updateTime where id =:id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(fieldSurveyTaskEntity);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void completeTask(String taskStatus,String id) {
        String sql="update ypg_survey_task set task_status = ? where id =?";
        jdbcTemplate.update(sql,taskStatus,id);
    }


    @Override
    public void assignGroup(FieldSurveyTaskEntity fieldSurveyTaskEntity) {
        String sql="update ypg_survey_task set group_id= :groupId ,group_leader = :groupLeader ,group_name = :groupName ," +
                "update_user = :updateUser ,update_time = :updateTime ,task_status = :taskStatus where id = :id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(fieldSurveyTaskEntity);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void deleteTask(String delFlag,String id) {
        String sql = "update ypg_survey_task set del_flag= ? where id = ?";
        jdbcTemplate.update(sql,delFlag,id);
    }

    @Override
    public void deleteFieldSurvey(String delFlag,String taskNum) {
        String sql = "update ypg_sampling_survey set del_flag= ? where task_code = ?";
        jdbcTemplate.update(sql,delFlag,taskNum);
    }

    @Override
    public Map<String, Object> getAppPage(HttpServletRequest request, TaskVo taskVo, int curPage, int pageSize,List<String> grouoIds) {
        StringBuilder sql = new StringBuilder("select * from ypg_survey_task");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0' ");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        int firstIndex=(curPage-1) * pageSize;
        int lastIndex=curPage * pageSize;
        StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        if(!PlatformObjectUtils.isEmpty(grouoIds)){
            whereSql.append(" and group_id in ('"+String.join("','", grouoIds)+"')");
        }
        if(!PlatformObjectUtils.isEmpty(taskVo.getTaskNum())){
            whereSql.append(" and task_num = '"+taskVo.getTaskNum()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(taskVo.getTaskName())){
            whereSql.append(" and task_name = '"+taskVo.getTaskName()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(taskVo.getTaskStatus())){
            whereSql.append(" and task_status = '"+taskVo.getTaskStatus()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(taskVo.getProvince()) || !PlatformObjectUtils.isEmpty(taskVo.getCity()) || !PlatformObjectUtils.isEmpty(taskVo.getCounty())){
            whereSql.append(" and (");
            if(!PlatformObjectUtils.isEmpty(taskVo.getProvince())){
                whereSql.append(" task_district like '%"+ taskVo.getProvince() +"%'");
            }
            if(!PlatformObjectUtils.isEmpty(taskVo.getCity())){
                whereSql.append(" or task_district like '%"+ taskVo.getCity() +"%'");
            }
            if(!PlatformObjectUtils.isEmpty(taskVo.getCounty())){
                whereSql.append(" or task_district like '%"+ taskVo.getCounty() +"%'");
            }
            whereSql.append(") ");
        }

        String sqlWhole = sql.append(whereSql).append(orderSql).append(limitSql).toString();
        List<FieldSurveyTaskEntity> GroupEntities = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(FieldSurveyTaskEntity.class));
        Integer total = jdbcTemplate.queryForObject("select count(*) from ypg_survey_task where del_flag = '0' and create_user = '"+taskVo.getCreateUser()+"' ", Integer.class);
        Map<String,Object> map=new HashMap();
        map.put("total",total);
        map.put("rows",GroupEntities);
        return map;
    }

    @Override
    public List<FieldSurveyVo> findSubIdByTask(String parentId, TaskVo taskVo) {
        StringBuilder sql = new StringBuilder("select * from ypg_survey_task");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0' and parent_id = '"+parentId+"'");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        if(!PlatformObjectUtils.isEmpty(taskVo.getCreateUser())){
            whereSql.append(" and create_user = '"+taskVo.getCreateUser()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(taskVo.getTaskNum())){
            whereSql.append(" and task_num like '%"+taskVo.getTaskNum()+"%'");
        }
        if(!PlatformObjectUtils.isEmpty(taskVo.getTaskName())){
            whereSql.append(" and task_name like '%"+taskVo.getTaskName()+"%'");
        }
        if(!PlatformObjectUtils.isEmpty(taskVo.getTaskStatus())){
            whereSql.append(" and task_status = '"+taskVo.getTaskStatus()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(taskVo.getProvince()) || !PlatformObjectUtils.isEmpty(taskVo.getCity()) || !PlatformObjectUtils.isEmpty(taskVo.getCounty())){
            whereSql.append(" and (");
            if(!PlatformObjectUtils.isEmpty(taskVo.getProvince())){
                whereSql.append(" task_district like '%"+ taskVo.getProvince() +"%'");
            }
            if(!PlatformObjectUtils.isEmpty(taskVo.getCity())){
                whereSql.append(" or task_district like '%"+ taskVo.getCity() +"%'");
            }
            if(!PlatformObjectUtils.isEmpty(taskVo.getCounty())){
                whereSql.append(" or task_district like '%"+ taskVo.getCounty() +"%'");
            }
            whereSql.append(") ");
        }
        String sqlWhole = sql.append(whereSql).append(orderSql).toString();
        List<FieldSurveyVo> list = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(FieldSurveyVo.class));
        return list;
    }

    @Override
    public FieldSurveyTaskEntity getParentTask(String parentTaskNum) {
        String sql="select * from ypg_survey_task where task_num = ? ";
        FieldSurveyTaskEntity fieldSurveyTaskEntity=new FieldSurveyTaskEntity();
        try {
            fieldSurveyTaskEntity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(FieldSurveyTaskEntity.class), parentTaskNum);
            return fieldSurveyTaskEntity;
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public String getMaxSubTaskCNum(String parentTaskNum) {
        String sql="select max(task_num) from ypg_survey_task where del_flag = '0' and parent_task = ?";
        String maxSubTaskCNum=jdbcTemplate.queryForObject(sql,String.class,parentTaskNum);
        return maxSubTaskCNum;
    }


}
